/**
 * 教师考勤审批模块数据库操作文件
 */

const { AUDIT_STATUS } = require('../utils/CONST');
const { query, pool, queryTrans, format, queryFirst, queryPage } = require('./common');

/**
 * 添加审批流程
 * @param {*} steps 步骤
 */
const addAuditFlow = steps => query(`INSERT INTO teacher_attendance_audit_flow_settings(attendance_id,teacher_id,step) VALUES ?`, [steps.map(({ attendance_id, teacher_id, step }) => [attendance_id, teacher_id, step])]);

/**
 * 是否存在相同的审批流程
 * @param {*} id 考勤类型
 * @returns 
 */
const existsFlow = id => queryFirst(`SELECT 1 FROM teacher_attendance_audit_flow_settings WHERE attendance_id = ? LIMIT 1`, [id]);

/**
 * 获取所有数据
 */
const getList = () => query(`SELECT t.*,d.name attendance_name,tt.name teacher_name FROM teacher_attendance_audit_flow_settings t
INNER JOIN dictionary d ON t.attendance_id = d.id
INNER JOIN teachers tt ON t.teacher_id = tt.id`);

/**
 * 修改审批流
 * @param {*} id 
 * @param {*} steps 
 * @returns 
 */
const updateAuditFlow = (id, steps) => queryTrans(format(`DELETE FROM teacher_attendance_audit_flow_settings WHERE attendance_id = ?`, [id]),
    format(`INSERT INTO teacher_attendance_audit_flow_settings(attendance_id,teacher_id,step) VALUES ?`, [steps.map(({ attendance_id, teacher_id, step }) => [attendance_id, teacher_id, step])]));

/**
 * 删除审批流
 * @param {*} type 考勤类型
 * @returns 
 */
const deleteAuditFlow = type => query('DELETE FROM teacher_attendance_audit_flow_settings WHERE attendance_id = ?', [type]);

/**
 * 申请考勤
 * @param {*} log 日志数据
 * @param {*} flow 审批流数据
 * @returns 
 */
const applyAttendance = (log, flow) => queryTrans(format(`INSERT INTO teacher_attendance_log SET ?`, log), format(`INSERT INTO teacher_attendance_audit_flow SET teacher_attendance_id = LAST_INSERT_ID(),?`, flow));

/**
 * 搜索我的考勤
 * @param {*} obj 
 */
const searchMyAttendances = obj => {
    const { attendance_id, status, teacher_id } = obj;
    const where = format(`WHERE log.teacher_id = ? AND (? = 0 OR log.attendance_id = ?) AND (? = 0 OR log.status_id = ?)`, [teacher_id, attendance_id, attendance_id, status, status]);
    return queryPage(obj, `SELECT * FROM(
        SELECT log.*,d.name attendance_name,d1.name status_name,
        c1.from_date ffrom_date,c1.end_date fend_date,c2.from_date efrom_date, c2.end_date eend_date,ROW_NUMBER() OVER(ORDER BY id DESC)num
        FROM teacher_attendance_log log
        INNER JOIN dictionary d ON log.attendance_id = d.id
        INNER JOIN dictionary d1 ON log.status_id = d1.id
        LEFT JOIN course_list c1 ON log.from_course_id = c1.id
        LEFT JOIN course_list c2 ON log.to_course_id = c2.id
        ${where})t
        WHERE t.num > ? AND t.num <= ?`,
        `SELECT COUNT(1) count FROM teacher_attendance_log log ${where}`);
}

/**
 * 搜索需要我审批的申请
 * @param {*} obj 
 */
const searchAuditApplies = id => query(`SELECT l.*,d.name attendance_name,d1.name status_name,t.name teacher_name,c.from_date ffrom_date,
c.end_date fend_date,c1.from_date efrom_date,c1.end_date eend_date,ROW_NUMBER() OVER(ORDER BY id DESC)num FROM teacher_attendance_log l
INNER JOIN teacher_attendance_audit_flow_settings s ON l.attendance_id = s.attendance_id AND l.step = s.step
INNER JOIN dictionary d ON l.attendance_id = d.id
INNER JOIN dictionary d1 ON l.status_id = d1.id
INNER JOIN teachers t ON l.teacher_id = t.id
LEFT JOIN course_list c ON l.from_course_id = c.id
LEFT JOIN course_list c1 ON l.to_course_id = c1.id
WHERE status_id = 36 AND s.teacher_id = ?`, [id]);

/**
 * 获取考勤审批流
 * @param {*} id 
 * @returns 
 */
const getAttendanceFlows = id => query(`SELECT f.*,d.name status_name,t.name audit_name FROM teacher_attendance_audit_flow f
INNER JOIN dictionary d ON f.status_id = d.id
LEFT JOIN teachers t ON f.audit_id = t.id
WHERE teacher_attendance_id = ?`, [id]);

/**
 * 是否为我的申请
 * @param {*} id 
 * @param {*} teacher_id 
 * @returns 
 */
const isMyLog = (id, teacher_id) => queryFirst(`SELECT * FROM teacher_attendance_log WHERE teacher_id = ? AND id = ?`, [teacher_id, id]);

/**
 * 获取审批教师
 * @returns 
 */
const getAuditTeachers = id => query(`SELECT s.teacher_id,t.name teacher_name FROM teacher_attendance_audit_flow_settings s
INNER JOIN teacher_attendance_log l ON s.attendance_id = l.attendance_id AND s.step = l.step
INNER JOIN teachers t ON s.teacher_id = t.id
WHERE l.id = ?`, [id]);

/**
 * 审批
 * @param {*} obj 
 */
const auditFlow = obj => {
    const { status_id, teacher_attendance_id, attendance_id, id, step, auditMessage, audit_id } = obj;
    const sqls = [format(`UPDATE teacher_attendance_audit_flow SET status_id = ?,date = CURRENT_TIMESTAMP(),auditMessage = ?,audit_id = ? WHERE id = ?`, [status_id, auditMessage, audit_id, id])];
    switch (parseInt(status_id)) {
        case AUDIT_STATUS.Fail:
            sqls.push(format(`UPDATE teacher_attendance_log SET status_id = ? WHERE id = ?`, [status_id, teacher_attendance_id]));
            break;
        case AUDIT_STATUS.Back:
            sqls.push(format(`UPDATE teacher_attendance_log SET status_id = ?,step = ? WHERE id = ?`, [AUDIT_STATUS.Replenish, step, teacher_attendance_id]));
            sqls.push(format(`INSERT INTO teacher_attendance_audit_flow SET ?`, { teacher_attendance_id, attendance_id, step, status_id: AUDIT_STATUS.Replenish }));
            break;
        case AUDIT_STATUS.Success:
            if (step) {
                sqls.push(format(`UPDATE teacher_attendance_log SET step = ? WHERE id = ?`, [step + 1, teacher_attendance_id]));
                sqls.push(format(`INSERT INTO teacher_attendance_audit_flow SET ?`, { teacher_attendance_id, attendance_id, step: step + 1, status_id: AUDIT_STATUS.Pending }));
            }
            else {
                sqls.push(format(`UPDATE teacher_attendance_log SET status_id = ? WHERE id = ?`, [status_id, teacher_attendance_id]));
            }
            break;
    }
    return queryTrans(...sqls);
}

/**
 * 根据id获取最后一个审批流
 * @param {*} id 
 * @returns 
 */
const getLastFlowById = id => queryFirst(`SELECT * FROM teacher_attendance_audit_flow
WHERE teacher_attendance_id = ?
ORDER BY id DESC
LIMIT 1`, [id]);

/**
 * 是否能审这一步
 * @param {*} attendance_id 
 * @param {*} step 
 * @param {*} teacher_id 
 * @returns 
 */
const canAuditThisStep = (attendance_id, step, teacher_id) => queryFirst(`SELECT 1 FROM teacher_attendance_audit_flow_settings WHERE attendance_id = ? AND step = ? AND teacher_id = ?`, [attendance_id, step, teacher_id]);

/**
 * 获取我的申请时间冲突
 * @param {*} teacher_id 
 * @param {*} from_date 
 * @param {*} end_date 
 * @returns 
 */
const getApplyIntersection = (teacher_id, from_date, end_date) => queryFirst(`SELECT from_date,end_date,attendance_id FROM teacher_attendance_log
WHERE teacher_id = ? AND status_id IN(${[AUDIT_STATUS.Pending, AUDIT_STATUS.Replenish, AUDIT_STATUS.Success].join()}) AND ((from_date >= ? AND from_date <= ?) OR (end_date >= ? AND end_date <= ?) OR (? >= from_date AND ? <= end_date)) 
LIMIT 1`, [teacher_id, from_date, end_date, from_date, end_date, from_date, end_date]);

/**
 * 根据考勤类型获取最大步骤
 * @param {*} attendance_id 
 * @returns 
 */
const getMaxStep = attendance_id => queryFirst(`SELECT MAX(step) step FROM teacher_attendance_audit_flow_settings WHERE attendance_id = ?`, [attendance_id]);

/**
 * 更新审批流
 * @param {*} param0 
 * @returns 
 */
const updateAttendanceFlow = ({ status_id, message, id, flowId }) => {
    const sqls = [format(`UPDATE teacher_attendance_log SET status_id = ? WHERE id = ?`, [status_id, id])];
    if (status_id == AUDIT_STATUS.Pending) {
        sqls.push(format(`UPDATE teacher_attendance_audit_flow SET status_id = ?,message = ? WHERE id = ?`, [status_id, message, flowId]));
    }
    else {
        sqls.push(format(`UPDATE teacher_attendance_audit_flow SET status_id = ? WHERE id = ?`, [status_id, flowId]));
    }
    return queryTrans(...sqls);
}

/**
 * 调换课程
 * @param {*} from_course_id 切换的课程ID
 * @param {*} to_course_id 调换的课程ID
 * @returns 
 */
const exChangeCourses = (from_course_id, to_course_id, ffrom_date, fend_date, efrom_date, eend_date) => queryTrans(format(`UPDATE course_list SET from_date = ?,end_date = ? WHERE id = ?`, [ffrom_date, fend_date, from_course_id]),
    format(`UPDATE course_list SET from_date = ?,end_date = ? WHERE id = ?`, [efrom_date, eend_date, to_course_id]));

/**
 * 获取课程老师ID
 * @param {*} id 
 * @returns 
 */
const getCouseTeacherIds = id => queryFirst(`SELECT from_course_id,to_course_id,c.from_date ffrom_date,c.end_date fend_date,c1.from_date efrom_date,c1.end_date eend_date FROM teacher_attendance_log t
INNER JOIN course_list c ON t.from_course_id = c.id
INNER JOIN course_list c1 ON t.to_course_id = c1.id
    WHERE t.id = ?`, [id]);

module.exports = {
    isMyLog,
    getList,
    auditFlow,
    existsFlow,
    getMaxStep,
    addAuditFlow,
    exChangeCourses,
    deleteAuditFlow,
    getLastFlowById,
    updateAuditFlow,
    applyAttendance,
    canAuditThisStep,
    getAuditTeachers,
    getAttendanceFlows,
    searchAuditApplies,
    getCouseTeacherIds,
    searchMyAttendances,
    getApplyIntersection,
    updateAttendanceFlow
}